﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DELVE_GAME
{
    class Database
    {
        // create the tables manually. Then all that is needed is to access/manipulate them.
        //use your own username and password to log in
        //run SQL> @create_tables.sql in oracle to make the tables. (file attached)
        //bool is whether connection succeeded or failed
        private SqlConnection connect()
        {
            //create connection
            try
            {
                //omitted my oracle username and password.
                //insert your own username and password
                //run SQL> @create_tables.sql in oracle to make the tables. (file attached)
                SqlConnection dbConnect = new SqlConnection("user id=USERNAME;"
                    + "pwd=PASSWORD;"
                    + "server=jdbc:oracle:thin:hr/hr@oracle.cise.ufl.edu:1521:orcl;"
                    + "Trusted_Connection=yes;"
                    + "database=delveDB;"
                    + "connection timeout=15");
                dbConnect.Open();
                return dbConnect;
            }
            catch (SqlException) { return null; }
        }

        /*
         * When a player clicks InternetGame they will be prompted to log into their player profile. 
         * Here you need to query the PLAYER database and see if they already exist, if they don't then create a new profile for them
         * (add to the PLAYER table).
         * //check if player exists, if yes verify log in info
         * //if does not exist, create new profile
         */
        public bool createProfile(String username, String password)
        {
            SqlConnection dbConnect = connect();
            if (dbConnect != null)
            {
                //verify username isn't already taken
                SqlCommand taken = new SqlCommand("SELECT Player.username FROM Player WHERE Player.username = '" + username + "'");
                if (taken == null)
                {
                    //insert into table if successful
                    SqlCommand insertNewPlayer = new SqlCommand("INSERT INTO Player (username, pwd) " +
                        "Values ('" + username + "', '" + password + "')", dbConnect);
                    insertNewPlayer.ExecuteNonQuery();
                    close(dbConnect);
                    return true;
                }
                else
                {
                    close(dbConnect);
                    return false;
                }
            }
            else return false;
        }

        public bool login(String username, String password)
        {
            SqlConnection dbConnect = connect();
            if (dbConnect != null)
            {
                //query for matching password and make sure equal to db value for login
                SqlCommand findPwd = new SqlCommand("SELECT Player.Password FROM Player WHERE Player.Username = '" + username + "'");
                SqlDataReader pwd = findPwd.ExecuteReader();
                String sPwd = null;
                while (pwd.Read())
                {
                    sPwd = (string)pwd["Password"];
                }
                pwd.Close();
                //passwords match, can log in...success
                if (password.Equals(sPwd))
                {
                    close(dbConnect);
                    return true;
                }
                //if fails, then incorrect username or password
                else
                {
                    close(dbConnect);
                    return false;
                }
            }
            //unable to connect
            else return false;
        }
        /*
         * The user will then be shown two options, "createGame" and "joinGame". 
         * It is at this point you should query the GAME table and see if there are any games that are accepting players, 
         * if not "joinGame" should be grayed out and not allow any users to select it. 
         * If the user is able to select joinGame, all of the games that are GameState=AcceptingPlayers should be displayed 
         * (for now we can display this simply by GameSID, in theory we could make it fancy and add other features). 
         */

        //GAMESTATE
        //0 = accepting players
        //1 = in progress
        //2 = ended

        public List<int> joinGame()
        {
            List<int> joinableGames= new List<int>();
            //connect and find all games that can be joined, put into list to return
            SqlConnection dbConnect = connect();
            if (dbConnect != null)
            {
                SqlCommand findJoinables = new SqlCommand("SELECT GameSID FROM Game WHERE GameState = 0");
                SqlDataReader joinables = findJoinables.ExecuteReader();
                while(joinables.Read())
                {
                    joinableGames.Add((int)joinables["GameSID"]);
                }
                close(dbConnect);
                joinables.Close();
            }
            //if empty, indicates either failure to connect or no available games
            return joinableGames;
        }
         /* If a user selects to join a specific game, an update query on PLAYER table needs to be called and 
         * CurrentLocation needs to be switched to the HostIP, an update query on GAME table needs to be called too, 
         * one of the PlayerSID's needs to be changed from NULL/-1 to said PlayerSID. 
         */ 

        //Player1SID reserved for game creator
        public bool playerManipulation(int GameSID, int PlayerSID){
            SqlConnection dbConnect = connect();
            //this will do ONE PLAYER at a time so game checking if it is full will be a separate method
            if (dbConnect != null)
            {
                //verify game is accepting players
                SqlCommand findVerify = new SqlCommand("SELECT GameState FROM Game WHERE GameSID = '" + GameSID + "'");
                SqlDataReader verify = findVerify.ExecuteReader();
                int gs = -1;
                while (verify.Read())
                {
                    gs = (int)verify["GameState"];
                }
                verify.Close();
                if (gs == 0)
                {
                    //set player.currentLocation of playerSID = game.HostIp of GameSID
                    SqlCommand findHostIp = new SqlCommand("SELECT hostIP FROM Game WHERE GameSID = '" + GameSID + "'");
                    SqlDataReader hostIp = findHostIp.ExecuteReader();
                    int ip = -1;
                    while (hostIp.Read())
                    {
                        ip = (int)hostIp["hostIP"];
                    }
                    hostIp.Close();
                    SqlCommand setCurrentLocation = new SqlCommand("UPDATE Player SET CurrentLocation = '" + ip + "' WHERE Player.PlayerSID = '" + PlayerSID + "'");
                    //set a valid null game.player#SID = playerSID
                    SqlCommand findSpot = new SqlCommand("SELECT * FROM GAME WHERE GameSID = '" + GameSID + "'");
                    SqlDataReader spot = findSpot.ExecuteReader();
                    int p2 = -1;
                    int p3 = -1;
                    int p4 = -1;
                    while (spot.Read())
                    {
                        p2 = (int)spot["Player2SID"];
                        p3 = (int)spot["Player3SID"];
                        p4 = (int)spot["Player4SID"];
                    }
                    spot.Close();
                    if (p2 != -1)
                    {
                        SqlCommand p2Update = new SqlCommand("UPDATE Game SET Player2SID = '" + PlayerSID + "' WHERE GameSID = '" + GameSID + "'");
                        p2Update.ExecuteNonQuery();
                        close(dbConnect);
                        return true;
                    }
                    else if (p3 != -1)
                    {
                        SqlCommand p3Update = new SqlCommand("UPDATE Game SET Player3SID = '" + PlayerSID + "' WHERE GameSID = '" + GameSID + "'");
                        p3Update.ExecuteNonQuery();
                        close(dbConnect);
                        return true;
                    }
                    else if (p4 != -1)
                    {
                        SqlCommand p4Update = new SqlCommand("UPDATE Game SET Player4SID = '" + PlayerSID + "' WHERE GameSID = '" + GameSID + "'");
                        p4Update.ExecuteNonQuery();
                        close(dbConnect);
                        return true;
                    }
                    else return false;
                }
                else
                {
                    close(dbConnect);
                    return false;
                }
            }
            else return false;
        }
        /* Every time the GAME table gets a new player, a check needs to be done to see if the GAME is full of players, 
        * if so, GameState needs to be updated to the correct int value. 
        * When that int changes, an alert needs be sent to the game to go to the playStage for all gamers.
        */
         public int currentGameState(int GameSID)
        {
            int gs = -1;
            SqlConnection dbConnect = connect();
            if (dbConnect != null)
            {
                //check gamestate value, return gamestate value
                SqlCommand findGsValue = new SqlCommand("SELECT GameState FROM Game WHERE GameSID = '"+GameSID+"'");
                SqlDataReader gsValue = findGsValue.ExecuteReader();
                while (gsValue.Read())
                {
                    gs = (int)gsValue["GameState"];
                }
                close(dbConnect);
                return gs;
            }
            else return gs;
        }
         /* If the user chooses "createGame" they will be prompted to select the gameSettings. 
         * Once they enter the gameSettings, a new GAME needs to be added to the GAME table and a new OPTIONS needs to be 
         * added to the OPTIONS table. The player who creates the game will also have their PLAYER table updated to show that 
         * their CurrentLocation is no longer null but now their hostIP.
         */
         public bool createGame(GameSettings game, int hostIP, int CreatorSID)
         {
            SqlConnection dbConnect = connect();
            if (dbConnect != null)
            {
                //find highest gameSID value and increment for new game
                SqlCommand findMaxGSID = new SqlCommand("MAX(Game.GameSID)");
                SqlDataReader maxGSID = findMaxGSID.ExecuteReader();
                int max = -1;
                while(maxGSID.Read())
                {
                    max = (int)maxGSID["GameSID"];
                }
                int GameID = max++;
                //insert new game into game table
                SqlCommand insertNewGame = new SqlCommand("INSERT INTO Game (GameSID, HostIP, GameState, Player1SID) " +
                    "Values ('" + GameID + "', '" + hostIP + "', 0, '"+CreatorSID+"')", dbConnect);
                insertNewGame.ExecuteNonQuery();
                //insert gamesettings into options table
                bool fow = game.fogOfWar;
                int fog=0;
                if (fow) fog = 1;
                SqlCommand insertNewOption = new SqlCommand("INSERT INTO Options (GameSID, MapName, GameType, NumPlayers, NumTurns, TurnTimer, Potency, Fow) " +
                    "Values ('" + GameID + "', '" + game.mapName + "', '"+game.gameType+"', '"+ game.numPlayers+"', '"+game.turnLimit +"', '"+game.timeLimit+"', "+game.startingPotencyGauge+"', '"+fog+"')", dbConnect);
                insertNewOption.ExecuteNonQuery();

                //update player.currentlocation to hostIp
                SqlCommand creatorLocation = new SqlCommand("UPDATE Player SET CurrentLocation = " + hostIP + "' WHERE Player.PlayerSID = '" + CreatorSID + "'");
                creatorLocation.ExecuteNonQuery();
                dbConnect.Close();
                return true;
            }
            else return false;
         }
         /* At the end of the game all players CurrentLocation's need to updated to null again. 
         * I think AcceptingPlayers should be an int instead of a bool because this will allow 0 to represent false, 
         * 1 to represent true (accepting players), 2 to represent game in progress, 3 to represent game already completed 
         * (You could rename this to GameState, and I guess 0 is no longer needed). Also you need Add a new field to STATS table 
         * representing the completion of said game. If the game was ended early (before victory) then the winner should be null.
        */

         public bool endGame(int GameSID, int winner/*PlayerSID of winner*/)
         {
              SqlConnection dbConnect = connect();
              if (dbConnect != null)
              {
                  //set current location of all players to null
                  //find players
                  SqlCommand findPlayers = new SqlCommand("SELECT * FROM Game WHERE GameSID = '"+GameSID+"'");
                  SqlDataReader players = findPlayers.ExecuteReader();
                  int p1 = -1;
                  int p2 = -1;
                  int p3 = -1;
                  int p4 = -1;
                  while (players.Read())
                  {
                      p1 = (int)players["Player1SID"];
                      p2 = (int)players["Player2SID"];
                      p3 = (int)players["Player3SID"];
                      p4 = (int)players["Player4SID"];
                  }
                  SqlCommand updateLocation1 = new SqlCommand("UPDATE Player SET CurrentLocation = " + null + "' WHERE Player.PlayerSID = '" + p1 + "'");
                  SqlCommand updateLocation2 = new SqlCommand("UPDATE Player SET CurrentLocation = " + null + "' WHERE Player.PlayerSID = '" + p2 + "'");
                  SqlCommand updateLocation3 = new SqlCommand("UPDATE Player SET CurrentLocation = " + null + "' WHERE Player.PlayerSID = '" + p3 + "'");
                  SqlCommand updateLocation4 = new SqlCommand("UPDATE Player SET CurrentLocation = " + null + "' WHERE Player.PlayerSID = '" + p4 + "'");
                  updateLocation1.ExecuteNonQuery();
                  updateLocation2.ExecuteNonQuery();
                  updateLocation3.ExecuteNonQuery();
                  updateLocation4.ExecuteNonQuery();

                  //GameState = 2 (finished game)
                  SqlCommand updateGS = new SqlCommand("UPDATE Game SET GameState = 2 WHERE GameSID + '"+GameSID+"'");
                  updateGS.ExecuteNonQuery();

                  //update winner in stats to player number or null
                  SqlCommand updateWin = new SqlCommand("UPDATE Stats SET Winner = '"+winner+"' WHERE GameSID = '"+GameSID+"'");
                  updateWin.ExecuteNonQuery();

                  close(dbConnect);
                  return true;
              }
             else return false;
         }

        private void close(SqlConnection dbConnect)
        {
            try{
                dbConnect.Close();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}
